package com.pe.pgn.clubpgn.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.common.CLPConstantes;
import com.pe.pgn.clubpgn.dao.SegmentacionDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClpbSegmentacion;
import com.pe.pgn.clubpgn.webapp.util.ValidationUtil;

@Repository("segmentacionDao")
public class SegmentacionDaoImpl extends GenericDaoHibernate<ClpbSegmentacion, Long> 
	implements SegmentacionDao{

	public SegmentacionDaoImpl() {
		super(ClpbSegmentacion.class);
	}
	
	private JdbcTemplate jdbcTemplate;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@SuppressWarnings("unchecked")
	public List<ClpbSegmentacion> obtenerSegmentaciones() {
	
		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" id, ");
		query.append(" condicion1, ");
		query.append(" condicion2, ");
		query.append(" parametro   ");
		query.append(" from clpb_segmentacion ");
		query.append(" where st_segmentacion = '").append(CLPConstantes.ST_VERDADERO).append("'");
		
		return findListOfBeans(query.toString(), null, ClpbSegmentacion.class);
	}

	@SuppressWarnings("unchecked")
	public ClpbSegmentacion obtenerClpbSegmentacionByID(Long id) {

		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" id, ");
		query.append(" condicion1, ");
		query.append(" condicion2, ");
		query.append(" parametro   ");
		query.append(" from clpb_segmentacion ");
		query.append(" where st_segmentacion = '").append(CLPConstantes.ST_VERDADERO).append("'");
		query.append(" and id = ").append(id);
		
		List<ClpbSegmentacion> list = findListOfBeans(query.toString(), null, ClpbSegmentacion.class);
		if(ValidationUtil.validateList(list)){
			return list.get(0);
		}else {
			return new ClpbSegmentacion();
		}
	}

	@SuppressWarnings("unchecked")
	public List<ClpbSegmentacion> busquedaSegmentacion(String parametro,
			String condicion1, String condicion2) {

		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" id, ");
		query.append(" condicion1, ");
		query.append(" condicion2, ");
		query.append(" parametro   ");
		query.append(" from clpb_segmentacion ");
		query.append(" where st_segmentacion = '").append(CLPConstantes.ST_VERDADERO).append("'");
		
		if(StringUtils.isNotBlank(parametro)){
			query.append(" and upper(parametro) = upper('").append(parametro.trim()).append("')");
		}
		
		if(StringUtils.isNotBlank(condicion1) && ValidationUtil.isNumber(condicion1)){
			query.append(" and condicion1 >= ").append(Double.parseDouble(condicion1));
		}
		
		if(StringUtils.isNotBlank(condicion2) && ValidationUtil.isNumber(condicion2)){
			query.append(" and condicion2 <= ").append(Double.parseDouble(condicion2));
		}
		
		return findListOfBeans(query.toString(), null, ClpbSegmentacion.class);
	}

	public void eliminarSegmento(Long id) {

		StringBuffer query = new StringBuffer();
		query.append(" delete from clpb_segmentacion");
		query.append(" where id = ").append(id);
		jdbcTemplate.update(query.toString());
	}

	public void guardarSegmentacion(ClpbSegmentacion clpbSegmentacion) {

		getHibernateTemplate().saveOrUpdate(clpbSegmentacion);
		getHibernateTemplate().flush();
	}

	public boolean validarTraslapoCondicion(ClpbSegmentacion clpbSegmentacion) {

		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" count(id) from clpb_segmentacion ");
		query.append(" where st_segmentacion = '").append(CLPConstantes.ST_VERDADERO).append("'");
		
		query.append(" and( ");
		
		query.append(" (condicion1 > ").append(clpbSegmentacion.getCondicion1());	
		query.append(" and condicion2 < ").append(clpbSegmentacion.getCondicion2());
		query.append(" ) or ");
		
		query.append(" (condicion1 = ").append(clpbSegmentacion.getCondicion1());	
		query.append(" and condicion2 = ").append(clpbSegmentacion.getCondicion2());
		query.append(" ) or ");
		
		query.append(" (condicion1 > ").append(clpbSegmentacion.getCondicion1());	
		query.append(" and condicion2 > ").append(clpbSegmentacion.getCondicion2());
		query.append(" and condicion1 < ").append(clpbSegmentacion.getCondicion2());
		query.append(" ) or ");
		
		query.append(" (condicion1 < ").append(clpbSegmentacion.getCondicion1());	
		query.append(" and condicion2 < ").append(clpbSegmentacion.getCondicion2());
		query.append(" and condicion2 > ").append(clpbSegmentacion.getCondicion1());	
		query.append(" ) or ");
		
		query.append(" (condicion1 < ").append(clpbSegmentacion.getCondicion1());	
		query.append(" and condicion2 > ").append(clpbSegmentacion.getCondicion2());
		query.append(" ) ");
		
		query.append(" )");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return false;
		}		
		return true;
	}
}
